/**
 * 
 */
package com.nari.baseinfo.utils.excelupload;

import java.io.File;
import java.math.BigDecimal;
import java.util.Date;
import java.util.Hashtable;
import java.util.Map;

import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;

import com.nari.baseinfo.utils.excelupload.parseentity.ExcelAssignMeterEntity;
import com.nari.baseinfo.utils.excelupload.parseentity.ExcelHeatSinkerEntity;
import com.nari.baseinfo.utils.excelupload.parseentity.ExcelRecordEntity;
import com.nari.baseinfo.utils.excelupload.parsetips.ExcelAssignMeterParserInfo;
import com.nari.baseinfo.utils.excelupload.parsetips.ExcelHeatSinkerParserInfo;
import com.nari.baseinfo.utils.excelupload.parsetips.ExcelRoomParserInfo;
import com.nari.platform.date.CalendarUtils;
import com.nari.platform.model.ResponseMsg;
import com.nari.platform.parser.excelparser.PoiExcelParser;
import com.nari.platform.utils.NumberSystemConversion;
import com.nari.platform.utils.StringUtils;

/**
 * 基础资料的Excel解析类
 * @author Xiaopeng
 * @date 2013-12-23
 */
public class BaseInfoExcelParser {
	private Map<Integer, ExcelRecordEntity> excelRecordMap = new Hashtable<Integer, ExcelRecordEntity>(0);
	
	/**
	 * 获取基础资料的Excel解析类的实例
	 * @return
	 * @author Xiaopeng
	 * @date 2013-12-23
	 */
	public static BaseInfoExcelParser getInstance() {
		return new BaseInfoExcelParser();
	}
	
	/**
	 * 获取Excel记录实体
	 * @param recordID
	 * @return
	 * @author Xiaopeng
	 * @date 2013-12-23
	 */
	public ExcelRecordEntity getRecord(int recordID) {
		return this.excelRecordMap.get(recordID);
	}
	
	/**
	 * 追加Excel记录实体
	 * @param recordEntity
	 * @return
	 * @author Xiaopeng
	 * @date 2013-12-23
	 */
	public ResponseMsg addRecord(ExcelRecordEntity recordEntity) {
		//基础资料的数据完整性校验
		ResponseMsg rmsg = BaseInfoExcelVerify.verifyExcelRecord(recordEntity);
		if(!rmsg.isSuccess()) return rmsg;
		//查询对应ID的数据是否已添加
		ExcelRecordEntity searchedRecord = this.getRecord(recordEntity.getRecordID());
		if(searchedRecord != null) {
			//基础资料的数据一致性校验
			rmsg = BaseInfoExcelVerify.verifyUserInfoUniform(searchedRecord, recordEntity);
			if(!rmsg.isSuccess()) return rmsg;
			//累加同一用户的分配表、散热器信息
			searchedRecord.getAssignMeterList().addAll(recordEntity.getAssignMeterList());
			searchedRecord.getHeatSinkerList().addAll(recordEntity.getHeatSinkerList());
		} else {
			//追加新的用户基本资料
			this.excelRecordMap.put(recordEntity.getRecordID(), recordEntity);
		}
		return ResponseMsg.getInstance(true, "");
	}
	
	/**
	 * 解析用户资料的Excel上传
	 * @param excelFile
	 * @param excelFileName
	 * @return
	 * @throws Exception
	 * @author Xiaopeng
	 * @date 2014-1-11
	 */
	public ResponseMsg parseBaseInfoExcel(File excelFile, String excelFileName) throws Exception {
		try {
			//读取Excel的第一张工作表
			Sheet sheet = PoiExcelParser.readExcelSheet(excelFile, excelFileName, 0);
			//循环遍历Excel的行数据
			for(int rowIndex=1; rowIndex<=sheet.getLastRowNum(); rowIndex++) {
				Row excelRow = sheet.getRow(rowIndex);
				//如果行为空白行，不进行处理
				if(PoiExcelParser.ifBlankRow(excelRow)) {
					continue;
				}
				//解析Excel的行数据
				ExcelParserInfo parserInfo = this.parseHeatRoomInfo(rowIndex, excelRow);
				//数据解析错误
				if(!parserInfo.getParserState().isSuccess()) {
					return parserInfo.getParserState();
				} else {
					//累加Excel解析结果
					ResponseMsg rmsg = this.addRecord(parserInfo.getRecordEntity());
					//Excel数据校验失败时，返回校验结果
					if(!rmsg.isSuccess()) {
						return rmsg;
					}
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception(e.getMessage());
		} finally {
			PoiExcelParser.freeExcelParserResource();
		}
		return ResponseMsg.getInstance(true, "");
	}
	
	/**
	 * 解析Excel的行数据，并返回封装的Excel记录实体
	 * @param rowIndex
	 * @param excelRow
	 * @return
	 * @throws Exception
	 * @author Xiaopeng
	 * @date 2013-12-23
	 */
	private ExcelRoomParserInfo parseHeatRoomInfo(int rowIndex, Row excelRow) throws Exception {
		ExcelRecordEntity recordEntity = new ExcelRecordEntity();
		ResponseMsg parserState = ResponseMsg.getInstance(true, "");
		//Excel的行统计值
		recordEntity.setRowCounter(rowIndex+1);
		//ID标识
		String recordIDStr = PoiExcelParser.readCellContent(excelRow.getCell(0));
		if(StringUtils.isNumberic(recordIDStr)) {
			Integer recordID = new Double(recordIDStr).intValue();
			recordEntity.setRecordID(recordID);
		}
		//用户姓名
		String userName = PoiExcelParser.readCellContent(excelRow.getCell(1));
		recordEntity.setUserName(userName);
		//联系电话
		String phone = PoiExcelParser.readCellContent(excelRow.getCell(2));
		if(StringUtils.isNumberic(phone) || StringUtils.isScientificNotation(phone)) {
			phone = NumberSystemConversion.formatToInteger(Double.parseDouble(phone));
		}
		recordEntity.setPhone(phone);
		//楼栋编号
		String buildingID = PoiExcelParser.readCellContent(excelRow.getCell(3));
		if(StringUtils.isNumberic(buildingID) || StringUtils.isScientificNotation(buildingID)) {
			buildingID = NumberSystemConversion.formatToInteger(Double.parseDouble(buildingID));
		} 
		recordEntity.setBuildingID(buildingID);
		//层号
		String floorIndex = PoiExcelParser.readCellContent(excelRow.getCell(4));
		if(StringUtils.isNumberic(floorIndex) || StringUtils.isScientificNotation(floorIndex)) {
			floorIndex = NumberSystemConversion.formatToInteger(Double.parseDouble(floorIndex));
		}
		recordEntity.setFloorIndex(floorIndex);
		//单元号
		String unitName = PoiExcelParser.readCellContent(excelRow.getCell(5));
		if(StringUtils.isNumberic(unitName) || StringUtils.isScientificNotation(unitName)) {
			unitName = NumberSystemConversion.formatToInteger(Double.parseDouble(unitName));
		}
		recordEntity.setUnitName(unitName);
		//房间号
		String houseNo =  PoiExcelParser.readCellContent(excelRow.getCell(6));
		if(StringUtils.isNumberic(houseNo) || StringUtils.isScientificNotation(houseNo)) {
			houseNo = NumberSystemConversion.formatToInteger(Double.parseDouble(houseNo));
		}
		recordEntity.setHouseNo(houseNo);
		//是否参与分摊
		String ifMeasureChargeStr = PoiExcelParser.readCellContent(excelRow.getCell(7));
		if(ifMeasureChargeStr != null) {
			ifMeasureChargeStr = ifMeasureChargeStr.trim();
			if("参与".equals(ifMeasureChargeStr)) {
				recordEntity.setIfMeasureCharge(2); 
			} 
			if("不参与".equals(ifMeasureChargeStr)) {
				recordEntity.setIfMeasureCharge(1); 
			}
		}
		//建筑面积
		String structAreaStr = PoiExcelParser.readCellContent(excelRow.getCell(8));
		if(!"".equals(structAreaStr) && !StringUtils.isNumberic(structAreaStr)) {
			parserState = ResponseMsg.getInstance(false, "请检查Excel中行号为【"+(rowIndex+1)+"】的<建筑面积>是否为数值型!");
			return ExcelRoomParserInfo.getInstance(parserState, recordEntity);
		} else if(StringUtils.isNumberic(structAreaStr)) {
			double structArea = new BigDecimal(structAreaStr).doubleValue();
			recordEntity.setStructArea(structArea);
		}
		//使用面积
		String heatAreaStr = PoiExcelParser.readCellContent(excelRow.getCell(9));
		if(!"".equals(heatAreaStr) && !StringUtils.isNumberic(heatAreaStr)) {
			parserState = ResponseMsg.getInstance(false, "请检查Excel中行号为【"+(rowIndex+1)+"】的<使用面积>是否为数值型!");
			return ExcelRoomParserInfo.getInstance(parserState, recordEntity);
		} else if(StringUtils.isNumberic(heatAreaStr)) {
			double heatArea = new BigDecimal(heatAreaStr).doubleValue();
			recordEntity.setHeatArea(heatArea);
		}
		//用途（楼栋功能）
		String houseFunctionStr = PoiExcelParser.readCellContent(excelRow.getCell(10));
		int houseFunction = 6; //其他
		if(houseFunctionStr != null) {
			houseFunctionStr = houseFunctionStr.trim();
			if(houseFunctionStr.startsWith("客厅") || houseFunctionStr.startsWith("客")) {
				houseFunction = 1;
			}
			if(houseFunctionStr.startsWith("卧室") || houseFunctionStr.startsWith("卧")) {
				houseFunction = 2;
			}
			if(houseFunctionStr.startsWith("厨房") || houseFunctionStr.startsWith("厨")) {
				houseFunction = 3;
			}
			if(houseFunctionStr.startsWith("厕所") || houseFunctionStr.startsWith("厕")) {
				houseFunction = 4;
			}
			if(houseFunctionStr.startsWith("阳台") || houseFunctionStr.startsWith("阳")) {
				houseFunction = 5;
			}
		}
		/**
		 * 1、参与分摊，或者不参与分摊却安装了分配表，都需要解析分配表和散热器；
		 * 2、不参与分摊，也没有安装分配表，则不需要解析散热器
		 *  **/
		//分配表表号
		String seriousNum = PoiExcelParser.readCellContent(excelRow.getCell(11));
		if(recordEntity.getIfMeasureCharge()==2 
				|| (seriousNum!=null && !"".equals(seriousNum.trim()))) { 
			//解析分配表数据
			ExcelAssignMeterParserInfo assignMeterParserInfo = this.parseAssignMeterInfo(rowIndex, excelRow);
			if(assignMeterParserInfo.getParserState().isSuccess()) {
				//设置用户资料所属分配表
				recordEntity.getAssignMeterList().add(assignMeterParserInfo.getAssignMeterEntity());
			} else {
				return ExcelRoomParserInfo.getInstance(assignMeterParserInfo.getParserState(), recordEntity);
			}
			//解析散热器数据
			ExcelHeatSinkerParserInfo sinkerParserInfo = this.parseHeatSinkerInfo(rowIndex, excelRow, seriousNum, houseFunction);
			if(sinkerParserInfo.getParserState().isSuccess()) {
				//设置用户资料所属散热器
				recordEntity.getHeatSinkerList().add(sinkerParserInfo.getSinkerEntity());
			} else {
				return ExcelRoomParserInfo.getInstance(sinkerParserInfo.getParserState(), recordEntity);
			}
		}
		//户修正系数
		String reviseFactorStr = PoiExcelParser.readCellContent(excelRow.getCell(32));
		if(reviseFactorStr==null || "".equals(reviseFactorStr.trim())) {
			recordEntity.setReviseFactor(1);
		}
		if(StringUtils.isNumberic(reviseFactorStr)) {
			double reviseFactor = new BigDecimal(reviseFactorStr).doubleValue();
			recordEntity.setReviseFactor(reviseFactor);
		}
		//33 散热器编号『忽略』
		//34 散热器是否参与分摊 『忽略』
		//楼层位置
		String floorPosition = PoiExcelParser.readCellContent(excelRow.getCell(35));
		if(floorPosition != null) {
			floorPosition = floorPosition.trim();
			if("顶层".equals(floorPosition)) {
				recordEntity.setFloorPosition("top");
			}
			if("中间层".equals(floorPosition)) {
				recordEntity.setFloorPosition("middle");
			}
			if("底层".equals(floorPosition)) {
				recordEntity.setFloorPosition("bottom");
			}
		}
		//是否为边户
		String ifSideRoom = PoiExcelParser.readCellContent(excelRow.getCell(36));
		if(ifSideRoom != null) {
			ifSideRoom = ifSideRoom.trim();
			if("是边户".equals(ifSideRoom)) {
				recordEntity.setIfSideRoom("1");
			}
			if("不是边户".equals(ifSideRoom)) {
				recordEntity.setIfSideRoom("2");
			}
		}
		//构造反馈信息
		return ExcelRoomParserInfo.getInstance(parserState, recordEntity);
	}
	
	/**
	 * 分配表信息解析
	 * @param rowIndex
	 * @param excelRow
	 * @return
	 * @throws Exception
	 * @author Xiaopeng
	 * @date 2014-1-21
	 */
	private ExcelAssignMeterParserInfo parseAssignMeterInfo(int rowIndex, 
			Row excelRow) throws Exception {
		ExcelAssignMeterEntity assignMeterEntity = new ExcelAssignMeterEntity();
		ResponseMsg parserState = ResponseMsg.getInstance(true, "");
		//分配表安装位置
		String houseFunctionStr = PoiExcelParser.readCellContent(excelRow.getCell(10));
		if(houseFunctionStr!=null&&!"".equals(houseFunctionStr)) {
			assignMeterEntity.setInstallPosition(houseFunctionStr);
		}
		//分配表表号
		String seriousNum = PoiExcelParser.readCellContent(excelRow.getCell(11));
		if(StringUtils.isNumberic(seriousNum) || StringUtils.isScientificNotation(seriousNum)) {
			seriousNum = NumberSystemConversion.formatToInteger(Double.parseDouble(seriousNum));
			seriousNum = seriousNum.trim();
		}
		assignMeterEntity.setSeriousNum(seriousNum.trim());//分配表表号注意空格
		//分配表型号
		String model = PoiExcelParser.readCellContent(excelRow.getCell(12));
		if(StringUtils.isNumberic(model) || StringUtils.isScientificNotation(model)) {
			model = NumberSystemConversion.formatToInteger(Double.parseDouble(model));
		}
		assignMeterEntity.setModel(model);
		//分配表软件版本号
		String softVersion = PoiExcelParser.readCellContent(excelRow.getCell(13));
		assignMeterEntity.setSoftVersion(softVersion);
		//分配表硬件版本号
		String hardVersion = PoiExcelParser.readCellContent(excelRow.getCell(14));
		assignMeterEntity.setHardVersion(hardVersion);
	
		//构造反馈信息
		return ExcelAssignMeterParserInfo.getInstance(parserState, assignMeterEntity);
	}
	
	/**
	 * 散热器信息解析
	 * @param rowIndex
	 * @param excelRow
	 * @param seriousNum
	 * @return
	 * @throws Exception
	 * @author Xiaopeng
	 * @date 2014-1-21
	 */
	private ExcelHeatSinkerParserInfo parseHeatSinkerInfo(int rowIndex, Row excelRow, 
			String seriousNum, int houseFunction) throws Exception {
		ExcelHeatSinkerEntity sinkerEntity = new ExcelHeatSinkerEntity();
		ResponseMsg parserState = ResponseMsg.getInstance(true, "");
		//散热器编号，对应分配表编号
		sinkerEntity.setSinkNo(seriousNum);
		//散热器型号
		String sinkType = PoiExcelParser.readCellContent(excelRow.getCell(15));
		if(StringUtils.isNumberic(sinkType) || StringUtils.isScientificNotation(sinkType)) {
			sinkType = NumberSystemConversion.formatToInteger(Double.parseDouble(sinkType));
		}
		sinkerEntity.setSinkType(StringUtils.filterSpecialChars(sinkType));
		//品名
		String brand = PoiExcelParser.readCellContent(excelRow.getCell(16));
		if(StringUtils.isNumberic(brand) || StringUtils.isScientificNotation(brand)) {
			brand = NumberSystemConversion.formatToInteger(Double.parseDouble(brand));
		}
		sinkerEntity.setBrand(StringUtils.filterSpecialChars(brand));
		//片数
		String pieceNumStr = PoiExcelParser.readCellContent(excelRow.getCell(17));
		if(!"".equals(pieceNumStr) && !StringUtils.isNumberic(pieceNumStr)) {
			parserState = ResponseMsg.getInstance(false, "请检查Excel中行号为【"+(rowIndex+1)+"】的<片数>是否为数值型!");
			return ExcelHeatSinkerParserInfo.getInstance(parserState, sinkerEntity);
		} else if(StringUtils.isNumberic(pieceNumStr)) {
			int pieceNum = new Double(pieceNumStr).intValue();
			sinkerEntity.setPieceNum(pieceNum);
		}
		//高
		String sinkHeightStr = PoiExcelParser.readCellContent(excelRow.getCell(18));
		if(!"".equals(sinkHeightStr) && !StringUtils.isNumberic(sinkHeightStr)) {
			parserState = ResponseMsg.getInstance(false, "请检查Excel中行号为【"+(rowIndex+1)+"】的<高>是否为数值型!");
			return ExcelHeatSinkerParserInfo.getInstance(parserState, sinkerEntity);
		} else if(StringUtils.isNumberic(sinkHeightStr)) {
			double sinkHeight = new BigDecimal(sinkHeightStr).doubleValue();
			sinkerEntity.setSinkHeight(sinkHeight);
		}
		//宽
		String sinkWidthStr = PoiExcelParser.readCellContent(excelRow.getCell(19));
		if(!"".equals(sinkWidthStr) && !StringUtils.isNumberic(sinkWidthStr)) {
			parserState = ResponseMsg.getInstance(false, "请检查Excel中行号为【"+(rowIndex+1)+"】的<宽>是否为数值型!");
			return ExcelHeatSinkerParserInfo.getInstance(parserState, sinkerEntity);
		} else if(StringUtils.isNumberic(sinkWidthStr)) {
			double sinkWidth = new BigDecimal(sinkWidthStr).doubleValue();
			sinkerEntity.setSinkWidth(sinkWidth);
		}
		//长
		String sinkLengthStr = PoiExcelParser.readCellContent(excelRow.getCell(20));
		if(!"".equals(sinkLengthStr) && !StringUtils.isNumberic(sinkLengthStr)) {
			parserState = ResponseMsg.getInstance(false, "请检查Excel中行号为【"+(rowIndex+1)+"】的<长>是否为数值型!");
			return ExcelHeatSinkerParserInfo.getInstance(parserState, sinkerEntity);
		} else if(StringUtils.isNumberic(sinkLengthStr)) {
			double sinkLength = new BigDecimal(sinkLengthStr).doubleValue();
			sinkerEntity.setSinkLength(sinkLength);
		}
		//中心矩
		String sinkCenDisStr = PoiExcelParser.readCellContent(excelRow.getCell(21));
		if(!"".equals(sinkCenDisStr) && !StringUtils.isNumberic(sinkCenDisStr)) {
			parserState = ResponseMsg.getInstance(false, "请检查Excel中行号为【"+(rowIndex+1)+"】的<中心距>是否为数值型!");
			return ExcelHeatSinkerParserInfo.getInstance(parserState, sinkerEntity);
		} else if(StringUtils.isNumberic(sinkCenDisStr)) {
			double sinkCenDis = new BigDecimal(sinkCenDisStr).doubleValue();
			sinkerEntity.setSinkCenDis(sinkCenDis);
		}
		//温控方式
		String tmpControlMode = PoiExcelParser.readCellContent(excelRow.getCell(22));
		sinkerEntity.setTmpControlMode(tmpControlMode);
		//是否有罩
		String isHasHatStr = PoiExcelParser.readCellContent(excelRow.getCell(23));
		if(isHasHatStr != null) {
			isHasHatStr = isHasHatStr.trim();
			if(isHasHatStr.startsWith("有")) {
				sinkerEntity.setIsHasHat("1");
			}
			if(isHasHatStr.startsWith("无")) {
				sinkerEntity.setIsHasHat("2");
			}
		}
		//24 照片号『忽略』
		//单片功率
		String calPowerStr = PoiExcelParser.readCellContent(excelRow.getCell(25));
		if(!"".equals(calPowerStr) && !StringUtils.isNumberic(calPowerStr)) {
			parserState = ResponseMsg.getInstance(false, "请检查Excel中行号为【"+(rowIndex+1)+"】的<单片功率>是否为数值型!");
			return ExcelHeatSinkerParserInfo.getInstance(parserState, sinkerEntity);
		} else if(StringUtils.isNumberic(calPowerStr)) {
			double calPower = new BigDecimal(calPowerStr).doubleValue();
			sinkerEntity.setCalPower(calPower);
		}
		//耦合修正系数Kc
		String kcFactorStr = PoiExcelParser.readCellContent(excelRow.getCell(26));
		if(!"".equals(kcFactorStr) && !StringUtils.isNumberic(kcFactorStr)) {
			parserState = ResponseMsg.getInstance(false, "请检查Excel中行号为【"+(rowIndex+1)+"】的<KC>是否为数值型!");
			return ExcelHeatSinkerParserInfo.getInstance(parserState, sinkerEntity);
		} else if(StringUtils.isNumberic(kcFactorStr)) {
			double kcFactor = new BigDecimal(kcFactorStr).doubleValue();
			sinkerEntity.setKcFactor(kcFactor);
		}
		//低温修正系数Kt
		String ktFactorStr = PoiExcelParser.readCellContent(excelRow.getCell(27));
		if(!"".equals(ktFactorStr) && !StringUtils.isNumberic(ktFactorStr)) {
			parserState = ResponseMsg.getInstance(false, "请检查Excel中行号为【"+(rowIndex+1)+"】的<KT>是否为数值型!");
			return ExcelHeatSinkerParserInfo.getInstance(parserState, sinkerEntity);
		} else if(StringUtils.isNumberic(ktFactorStr)) {
			double ktFactor = new BigDecimal(ktFactorStr).doubleValue();
			sinkerEntity.setKtFactor(ktFactor);
		}
		//用途（楼栋功能）
		sinkerEntity.setHouseFunction(houseFunction);
		//安装人
		String installName = PoiExcelParser.readCellContent(excelRow.getCell(28));
		sinkerEntity.setInstallName(installName);
		//记录人
		String recordName = PoiExcelParser.readCellContent(excelRow.getCell(29));
		sinkerEntity.setRecordName(recordName);
		//安装时间
		String installTimeStr = PoiExcelParser.readCellContent(excelRow.getCell(30));
		if(installTimeStr!=null && !"".equals(installTimeStr.trim())) {
			if(StringUtils.isCommonCNDateFormat(installTimeStr.trim())) {
				Date installTime = CalendarUtils.parseStringToDate(installTimeStr.trim());
				sinkerEntity.setInstallTime(installTime);
			} else {
				parserState = ResponseMsg.getInstance(false, "请检查Excel中行号为【"+(rowIndex+1)+"】的<安装时间>填写了不正确的时间格式，正确的时间格式为：2014-01-01");
				return ExcelHeatSinkerParserInfo.getInstance(parserState, sinkerEntity);
			}
		}
		//备注
		String remark = PoiExcelParser.readCellContent(excelRow.getCell(31));
		if(StringUtils.isNumberic(remark) || StringUtils.isScientificNotation(remark)) {
			remark = NumberSystemConversion.formatToDecimalNumber(Double.parseDouble(remark));
		}
		//处理特殊字符
		remark = StringUtils.filterSpecialChars(remark);
		sinkerEntity.setRemrak(remark);
		//构造反馈信息
		return ExcelHeatSinkerParserInfo.getInstance(parserState, sinkerEntity);
	}
	
	/**
	 * 获取用户资料的Excel解析结果
	 * @return
	 * @author Xiaopeng
	 * @date 2013-12-23
	 */
	public Map<Integer, ExcelRecordEntity> getExcelParseData() {
		return this.excelRecordMap;
	}
}
